www.gusucode.com > PHP版金融投资类企业PHP源码程序 > PHP版金融投资类企业/公司网站系统源码 v1.0/IVEARS_v1.0/IVEARS_v1.0/ThinkPHP/Extend/Mode/Cli/Db.class.php
<?php
// +----------------------------------------------------------------------
// | ThinkPHP [ WE CAN DO IT JUST THINK IT ]
// +----------------------------------------------------------------------
// | Copyright (c) 2006-2012 http://thinkphp.cn All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: liu21st <liu21st@gmail.com>
// +----------------------------------------------------------------------
define('CLIENT_MULTI_RESULTS', 131072);
/**
* ThinkPHP 精简模式数据库中间层实现类 只支持Mysql
*/
class Db {
static private $_instance = null;
// 是否自动释放查询结果
protected $autoFree = false;
// 是否显示调试信息 如果启用会在日志文件记录sql语句
public $debug = false;
// 是否使用永久连接
protected $pconnect = false;
// 当前SQL指令
protected $queryStr = '';
// 最后插入ID
protected $lastInsID = null;
// 返回或者影响记录数
protected $numRows = 0;
// 返回字段数
protected $numCols = 0;
// 事务指令数
protected $transTimes = 0;
// 错误信息
protected $error = '';
// 当前连接ID
protected $linkID = null;
// 当前查询ID
protected $queryID = null;
// 是否已经连接数据库
protected $connected = false;
// 数据库连接参数配置
protected $config = '';
// 数据库表达式
protected $comparison = array('eq'=>'=','neq'=>'!=','gt'=>'>','egt'=>'>=','lt'=>'<','elt'=>'<=','notlike'=>'NOT LIKE','like'=>'LIKE');
// 查询表达式
protected $selectSql = 'SELECT%DISTINCT% %FIELDS% FROM %TABLE%%JOIN%%WHERE%%GROUP%%HAVING%%ORDER%%LIMIT%';
/**
* 架构函数
* @access public
* @param array $config 数据库配置数组
*/
public function __construct($config=''){
if ( !extension_loaded('mysql') ) {
throw_exception(L('_NOT_SUPPERT_').':mysql');
}
$this->config = $this->parseConfig($config);
if(APP_DEBUG) {
$this->debug = true;
}
}
/**
* 连接数据库方法
* @access public
* @throws ThinkExecption
*/
public function connect() {
if(!$this->connected) {
$config = $this->config;
// 处理不带端口号的socket连接情况
$host = $config['hostname'].($config['hostport']?":{$config['hostport']}":'');
$pconnect = !empty($config['params']['persist'])? $config['params']['persist']:$this->pconnect;
if($pconnect) {
$this->linkID = mysql_pconnect( $host, $config['username'], $config['password'],CLIENT_MULTI_RESULTS);
}else{
$this->linkID = mysql_connect( $host, $config['username'], $config['password'],true,CLIENT_MULTI_RESULTS);
}
if ( !$this->linkID || (!empty($config['database']) && !mysql_select_db($config['database'], $this->linkID)) ) {
throw_exception(mysql_error());
}
$dbVersion = mysql_get_server_info($this->linkID);
if ($dbVersion >= "4.1") {
//使用UTF8存取数据库 需要mysql 4.1.0以上支持
mysql_query("SET NAMES '".C('DB_CHARSET')."'", $this->linkID);
}
//设置 sql_model
if($dbVersion >'5.0.1'){
mysql_query("SET sql_mode=''",$this->linkID);
}
// 标记连接成功
$this->connected = true;
// 注销数据库连接配置信息
unset($this->config);
}
}
/**
* 释放查询结果
* @access public
*/
public function free() {
mysql_free_result($this->queryID);
$this->queryID = 0;
}
/**
* 执行查询 主要针对 SELECT, SHOW 等指令
* 返回数据集
* @access public
* @param string $str sql指令
* @return mixed
* @throws ThinkExecption
*/
public function query($str='') {
$this->connect();
if ( !$this->linkID ) return false;
if ( $str != '' ) $this->queryStr = $str;
//释放前次的查询结果
if ( $this->queryID ) { $this->free(); }
N('db_query',1);
// 记录开始执行时间
G('queryStartTime');
$this->queryID = mysql_query($this->queryStr, $this->linkID);
$this->debug();
if ( !$this->queryID ) {
if ( $this->debug )
throw_exception($this->error());
else
return false;
} else {
$this->numRows = mysql_num_rows($this->queryID);
return $this->getAll();
}
}
/**
* 执行语句 针对 INSERT, UPDATE 以及DELETE
* @access public
* @param string $str sql指令
* @return integer
* @throws ThinkExecption
*/
public function execute($str='') {
$this->connect();
if ( !$this->linkID ) return false;
if ( $str != '' ) $this->queryStr = $str;
//释放前次的查询结果
if ( $this->queryID ) { $this->free(); }
N('db_write',1);
$result = mysql_query($this->queryStr, $this->linkID) ;
$this->debug();
if ( false === $result) {
if ( $this->debug )
throw_exception($this->error());
else
return false;
} else {
$this->numRows = mysql_affected_rows($this->linkID);
$this->lastInsID = mysql_insert_id($this->linkID);
return $this->numRows;
}
}
/**
* 启动事务
* @access public
* @return void
* @throws ThinkExecption
*/
public function startTrans() {
$this->connect(true);
if ( !$this->linkID ) return false;
//数据rollback 支持
if ($this->transTimes == 0) {
mysql_query('START TRANSACTION', $this->linkID);
}
$this->transTimes++;
return ;
}
/**
* 用于非自动提交状态下面的查询提交
* @access public
* @return boolen
* @throws ThinkExecption
*/
public function commit() {
if ($this->transTimes > 0) {
$result = mysql_query('COMMIT', $this->linkID);
$this->transTimes = 0;
if(!$result){
throw_exception($this->error());
return false;
}
}
return true;
}
/**
* 事务回滚
* @access public
* @return boolen
* @throws ThinkExecption
*/
public function rollback() {
if ($this->transTimes > 0) {
$result = mysql_query('ROLLBACK', $this->linkID);
$this->transTimes = 0;
if(!$result){
throw_exception($this->error());
return false;
}
}
return true;
}
/**
* 获得所有的查询数据
* @access public
* @return array
* @throws ThinkExecption
*/
public function getAll() {
if ( !$this->queryID ) {
throw_exception($this->error());
return false;
}
//返回数据集
$result = array();
if($this->numRows >0) {
while($row = mysql_fetch_assoc($this->queryID)){
$result[] = $row;
}
mysql_data_seek($this->queryID,0);
}
return $result;
}
/**
* 取得数据表的字段信息
* @access public
*/
public function getFields($tableName) {
$result = $this->query('SHOW COLUMNS FROM '.$tableName);
$info = array();
foreach ($result as $key => $val) {
$info[$val['Field']] = array(
'name' => $val['Field'],
'type' => $val['Type'],
'notnull' => (bool) ($val['Null'] === ''), // not null is empty, null is yes
'default' => $val['Default'],
'primary' => (strtolower($val['Key']) == 'pri'),
'autoinc' => (strtolower($val['Extra']) == 'auto_increment'),
);
}
return $info;
}
/**
* 取得数据库的表信息
* @access public
*/
public function getTables($dbName='') {
if(!empty($dbName)) {
$sql = 'SHOW TABLES FROM '.$dbName;
}else{
$sql = 'SHOW TABLES ';
}
$result = $this->query($sql);
$info = array();
foreach ($result as $key => $val) {
$info[$key] = current($val);
}
return $info;
}
/**
* 关闭数据库
* @access public
* @throws ThinkExecption
*/
public function close() {
if (!empty($this->queryID))
mysql_free_result($this->queryID);
if ($this->linkID && !mysql_close($this->linkID)){
throw_exception($this->error());
}
$this->linkID = 0;
}
/**
* 数据库错误信息
* 并显示当前的SQL语句
* @access public
* @return string
*/
public function error() {
$this->error = mysql_error($this->linkID);
if($this->queryStr!=''){
$this->error .= "\n [ SQL语句 ] : ".$this->queryStr;
}
return $this->error;
}
/**
* SQL指令安全过滤
* @access public
* @param string $str SQL字符串
* @return string
*/
public function escapeString($str) {
return mysql_escape_string($str);
}
/**
* 析构方法
* @access public
*/
public function __destruct() {
// 关闭连接
$this->close();
}
/**
* 取得数据库类实例
* @static
* @access public
* @return mixed 返回数据库驱动类
*/
public static function getInstance($db_config='') {
if ( self::$_instance==null ){
self::$_instance = new Db($db_config);
}
return self::$_instance;
}
/**
* 分析数据库配置信息，支持数组和DSN
* @access private
* @param mixed $db_config 数据库配置信息
* @return string
*/
private function parseConfig($db_config='') {
if ( !empty($db_config) && is_string($db_config)) {
// 如果DSN字符串则进行解析
$db_config = $this->parseDSN($db_config);
}else if(empty($db_config)){
// 如果配置为空，读取配置文件设置
$db_config = array (
'dbms' => C('DB_TYPE'),
'username' => C('DB_USER'),
'password' => C('DB_PWD'),
'hostname' => C('DB_HOST'),
'hostport' => C('DB_PORT'),
'database' => C('DB_NAME'),
'dsn' => C('DB_DSN'),
'params' => C('DB_PARAMS'),
);
}
return $db_config;
}
/**
* DSN解析
* 格式： mysql://username:passwd@localhost:3306/DbName
* @static
* @access public
* @param string $dsnStr
* @return array
*/
public function parseDSN($dsnStr) {
if( empty($dsnStr) ){return false;}
$info = parse_url($dsnStr);
if($info['scheme']){
$dsn = array(
'dbms' => $info['scheme'],
'username' => isset($info['user']) ? $info['user'] : '',
'password' => isset($info['pass']) ? $info['pass'] : '',
'hostname' => isset($info['host']) ? $info['host'] : '',
'hostport' => isset($info['port']) ? $info['port'] : '',
'database' => isset($info['path']) ? substr($info['path'],1) : ''
);
}else {
preg_match('/^(.*?)\:\/\/(.*?)\:(.*?)\@(.*?)\:([0-9]{1, 6})\/(.*?)$/',trim($dsnStr),$matches);
$dsn = array (
'dbms' => $matches[1],
'username' => $matches[2],
'password' => $matches[3],
'hostname' => $matches[4],
'hostport' => $matches[5],
'database' => $matches[6]
);
}
return $dsn;
}
/**
* 数据库调试 记录当前SQL
* @access protected
*/
protected function debug() {
// 记录操作结束时间
if ( $this->debug ) {
G('queryEndTime');
Log::record($this->queryStr." [ RunTime:".G('queryStartTime','queryEndTime',6)."s ]",Log::SQL);
}
}
/**
* 设置锁机制
* @access protected
* @return string
*/
protected function parseLock($lock=false) {
if(!$lock) return '';
if('ORACLE' == $this->dbType) {
return ' FOR UPDATE NOWAIT ';
}
return ' FOR UPDATE ';
}
/**
* set分析
* @access protected
* @param array $data
* @return string
*/
protected function parseSet($data) {
foreach ($data as $key=>$val){
$value = $this->parseValue($val);
if(is_scalar($value)) // 过滤非标量数据
$set[] = $this->parseKey($key).'='.$value;
}
return ' SET '.implode(',',$set);
}
/**
* value分析
* @access protected
* @param mixed $value
* @return string
*/
protected function parseValue($value) {
if(is_string($value)) {
$value = '\''.$this->escapeString($value).'\'';
}elseif(isset($value[0]) && is_string($value[0]) && strtolower($value[0]) == 'exp'){
$value = $this->escapeString($value[1]);
}elseif(is_null($value)){
$value = 'null';
}
return $value;
}
/**
* field分析
* @access protected
* @param mixed $fields
* @return string
*/
protected function parseField($fields) {
if(is_array($fields)) {
// 完善数组方式传字段名的支持
// 支持 'field1'=>'field2' 这样的字段别名定义
$array = array();
foreach ($fields as $key=>$field){
if(!is_numeric($key))
$array[] = $this->parseKey($key).' AS '.$this->parseKey($field);
else
$array[] = $this->parseKey($field);
}
$fieldsStr = implode(',', $array);
}elseif(is_string($fields) && !empty($fields)) {
$fieldsStr = $this->parseKey($fields);
}else{
$fieldsStr = '*';
}
return $fieldsStr;
}
/**
* table分析
* @access protected
* @param mixed $table
* @return string
*/
protected function parseTable($tables) {
if(is_string($tables))
$tables = explode(',',$tables);
array_walk($tables, array(&$this, 'parseKey'));
return implode(',',$tables);
}
/**
* where分析
* @access protected
* @param mixed $where
* @return string
*/
protected function parseWhere($where) {
$whereStr = '';
if(is_string($where)) {
// 直接使用字符串条件
$whereStr = $where;
}else{ // 使用数组条件表达式
if(isset($where['_logic'])) {
// 定义逻辑运算规则 例如 OR XOR AND NOT
$operate = ' '.strtoupper($where['_logic']).' ';
unset($where['_logic']);
}else{
// 默认进行 AND 运算
$operate = ' AND ';
}
foreach ($where as $key=>$val){
$whereStr .= "( ";
if(0===strpos($key,'_')) {
// 解析特殊条件表达式
$whereStr .= $this->parseThinkWhere($key,$val);
}else{
$key = $this->parseKey($key);
if(is_array($val)) {
if(is_string($val[0])) {
if(preg_match('/^(EQ|NEQ|GT|EGT|LT|ELT|NOTLIKE|LIKE)$/i',$val[0])) { // 比较运算
$whereStr .= $key.' '.$this->comparison[strtolower($val[0])].' '.$this->parseValue($val[1]);
}elseif('exp'==strtolower($val[0])){ // 使用表达式
$whereStr .= ' ('.$key.' '.$val[1].') ';
}elseif(preg_match('/IN/i',$val[0])){ // IN 运算
$zone = is_array($val[1])? implode(',',$this->parseValue($val[1])):$val[1];
$whereStr .= $key.' '.strtoupper($val[0]).' ('.$zone.')';
}elseif(preg_match('/BETWEEN/i',$val[0])){ // BETWEEN运算
$data = is_string($val[1])? explode(',',$val[1]):$val[1];
$whereStr .= ' ('.$key.' BETWEEN '.$data[0].' AND '.$data[1].' )';
}else{
throw_exception(L('_EXPRESS_ERROR_').':'.$val[0]);
}
}else {
$count = count($val);
if(in_array(strtoupper(trim($val[$count-1])),array('AND','OR','XOR'))) {
$rule = strtoupper(trim($val[$count-1]));
$count = $count -1;
}else{
$rule = 'AND';
}
for($i=0;$i<$count;$i++) {
$data = is_array($val[$i])?$val[$i][1]:$val[$i];
if('exp'==strtolower($val[$i][0])) {
$whereStr .= '('.$key.' '.$data.') '.$rule.' ';
}else{
$op = is_array($val[$i])?$this->comparison[strtolower($val[$i][0])]:'=';
$whereStr .= '('.$key.' '.$op.' '.$this->parseValue($data).') '.$rule.' ';
}
}
$whereStr = substr($whereStr,0,-4);
}
}else {
//对字符串类型字段采用模糊匹配
if(C('LIKE_MATCH_FIELDS') && preg_match('/('.C('LIKE_MATCH_FIELDS').')/i',$key)) {
$val = '%'.$val.'%';
$whereStr .= $key." LIKE ".$this->parseValue($val);
}else {
$whereStr .= $key." = ".$this->parseValue($val);
}
}
}
$whereStr .= ' )'.$operate;
}
$whereStr = substr($whereStr,0,-strlen($operate));
}
return empty($whereStr)?'':' WHERE '.$whereStr;
}
/**
* 特殊条件分析
* @access protected
* @param string $key
* @param mixed $val
* @return string
*/
protected function parseThinkWhere($key,$val) {
$whereStr = '';
switch($key) {
case '_string':
// 字符串模式查询条件
$whereStr = $val;
break;
case '_complex':
// 复合查询条件
$whereStr = substr($this->parseWhere($val),6);
break;
case '_query':
// 字符串模式查询条件
parse_str($val,$where);
if(isset($where['_logic'])) {
$op = ' '.strtoupper($where['_logic']).' ';
unset($where['_logic']);
}else{
$op = ' AND ';
}
$array = array();
foreach ($where as $field=>$data)
$array[] = $this->parseKey($field).' = '.$this->parseValue($data);
$whereStr = implode($op,$array);
break;
}
return $whereStr;
}
/**
* limit分析
* @access protected
* @param mixed $lmit
* @return string
*/
protected function parseLimit($limit) {
return !empty($limit)? ' LIMIT '.$limit.' ':'';
}
/**
* join分析
* @access protected
* @param mixed $join
* @return string
*/
protected function parseJoin($join) {
$joinStr = '';
if(!empty($join)) {
if(is_array($join)) {
foreach ($join as $key=>$_join){
if(false !== stripos($_join,'JOIN'))
$joinStr .= ' '.$_join;
else
$joinStr .= ' LEFT JOIN ' .$_join;
}
}else{
$joinStr .= ' LEFT JOIN ' .$join;
}
}
return $joinStr;
}
/**
* order分析
* @access protected
* @param mixed $order
* @return string
*/
protected function parseOrder($order) {
return !empty($order)? ' ORDER BY '.$order:'';
}
/**
* group分析
* @access protected
* @param mixed $group
* @return string
*/
protected function parseGroup($group) {
return !empty($group)? ' GROUP BY '.$group:'';
}
/**
* having分析
* @access protected
* @param string $having
* @return string
*/
protected function parseHaving($having) {
return !empty($having)? ' HAVING '.$having:'';
}
/**
* distinct分析
* @access protected
* @param mixed $distinct
* @return string
*/
protected function parseDistinct($distinct) {
return !empty($distinct)? ' DISTINCT ' :'';
}
/**
* 插入记录
* @access public
* @param mixed $data 数据
* @param array $options 参数表达式
* @return false | integer
*/
public function insert($data,$options=array()) {
foreach ($data as $key=>$val){
$value = $this->parseValue($val);
if(is_scalar($value)) { // 过滤非标量数据
$values[] = $value;
$fields[] = $this->parseKey($key);
}
}
$sql = 'INSERT INTO '.$this->parseTable($options['table']).' ('.implode(',', $fields).') VALUES ('.implode(',', $values).')';
$sql .= $this->parseLock(isset($options['lock'])?$options['lock']:false);
return $this->execute($sql);
}
/**
* 更新记录
* @access public
* @param mixed $data 数据
* @param array $options 表达式
* @return false | integer
*/
public function update($data,$options) {
$sql = 'UPDATE '
.$this->parseTable($options['table'])
.$this->parseSet($data)
.$this->parseWhere(isset($options['where'])?$options['where']:'')
.$this->parseOrder(isset($options['order'])?$options['order']:'')
.$this->parseLimit(isset($options['limit'])?$options['limit']:'')
.$this->parseLock(isset($options['lock'])?$options['lock']:false);
return $this->execute($sql);
}
/**
* 删除记录
* @access public
* @param array $options 表达式
* @return false | integer
*/
public function delete($options=array()) {
$sql = 'DELETE FROM '
.$this->parseTable($options['table'])
.$this->parseWhere(isset($options['where'])?$options['where']:'')
.$this->parseOrder(isset($options['order'])?$options['order']:'')
.$this->parseLimit(isset($options['limit'])?$options['limit']:'')
.$this->parseLock(isset($options['lock'])?$options['lock']:false);
return $this->execute($sql);
}
/**
* 查找记录
* @access public
* @param array $options 表达式
* @return array
*/
public function select($options=array()) {
if(isset($options['page'])) {
// 根据页数计算limit
list($page,$listRows) = explode(',',$options['page']);
$listRows = $listRows?$listRows:($options['limit']?$options['limit']:20);
$offset = $listRows*((int)$page-1);
$options['limit'] = $offset.','.$listRows;
}
$sql = str_replace(
array('%TABLE%','%DISTINCT%','%FIELDS%','%JOIN%','%WHERE%','%GROUP%','%HAVING%','%ORDER%','%LIMIT%'),
array(
$this->parseTable($options['table']),
$this->parseDistinct(isset($options['distinct'])?$options['distinct']:false),
$this->parseField(isset($options['field'])?$options['field']:'*'),
$this->parseJoin(isset($options['join'])?$options['join']:''),
$this->parseWhere(isset($options['where'])?$options['where']:''),
$this->parseGroup(isset($options['group'])?$options['group']:''),
$this->parseHaving(isset($options['having'])?$options['having']:''),
$this->parseOrder(isset($options['order'])?$options['order']:''),
$this->parseLimit(isset($options['limit'])?$options['limit']:'')
),$this->selectSql);
$sql .= $this->parseLock(isset($options['lock'])?$options['lock']:false);
return $this->query($sql);
}
/**
* 字段和表名添加`
* 保证指令中使用关键字不出错 针对mysql
* @access protected
* @param mixed $value
* @return mixed
*/
protected function parseKey(&$value) {
$value = trim($value);
if( false !== strpos($value,' ') || false !== strpos($value,',') || false !== strpos($value,'*') || false !== strpos($value,'(') || false !== strpos($value,'.') || false !== strpos($value,'`')) {
//如果包含* 或者 使用了sql方法 则不作处理
}else{
$value = '`'.$value.'`';
}
return $value;
}
/**
* 获取最近一次查询的sql语句
* @access public
* @return string
*/
public function getLastSql() {
return $this->queryStr;
}
/**
* 获取最近插入的ID
* @access public
* @return string
*/
public function getLastInsID(){
return $this->lastInsID;
}
}